
package net.swa.dewei.service.impl;

import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.swa.dewei.beans.entity.CheXing;
import net.swa.dewei.beans.entity.ChenxingCityRelation;
import net.swa.dewei.service.CheXingCityService;
import net.swa.system.dao.HibernateDaoSupport;
import net.swa.util.BuzConst;
import net.swa.util.JsonResult;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Service;

@Service("cheXingCityService")
public class CheXingCityServiceImpl extends HibernateDaoSupport implements CheXingCityService
{

    private static Logger log = Logger.getLogger(CheXingCityServiceImpl.class);

    @SuppressWarnings("unchecked")
    @Override
    public JsonResult<ChenxingCityRelation> queryCheXingPage(String province , String city , String town ,
            String chexingName , int currentPage , int pageSize , String orderBy , String orderType)
    {
        if (log.isDebugEnabled())
        {
            log.debug("传入参数 province is" + province + " ,city is " + city + ", town is " + town + ",chexingName is "
                    + chexingName);
        }
        JsonResult<ChenxingCityRelation> json = new JsonResult<ChenxingCityRelation>();
        StringBuilder sql = new StringBuilder();
        StringBuilder countsql = new StringBuilder();
        Query query = null;
        countsql
                .append("select count(1) as counNum  from    t_Chexing_City  r join t_CheXing c on r.chexingId=c.id  where 1=1  ");
        sql
                .append("select r.chexingId, r.city,r.crateDate,r.id,c.imageUrl,c.name,r.province,r.sort,r.town,r.updateDate from  t_Chexing_City  r join t_CheXing c on r.chexingId=c.id  where 1=1  ");
        //        sql.append("select r.chexingId, r.city,r.crateDate,r.id,r.imageUrl,c.name,r.province,r.sort,r.town,r.updateDate from  t_Chexing_City  r join t_CheXing c on r.chexingId=c.id  where 1=1 ");

        if (!StringUtils.isBlank(province))
        {
            sql.append(" and r.province =:province");
            countsql.append(" and r.province =:province");
        }
        if (!StringUtils.isBlank(city))
        {
            sql.append(" and r.city =:city");
            countsql.append(" and r.city =:city");
        }
        if (!StringUtils.isBlank(town))
        {
            sql.append(" and r.town=:town");
            countsql.append(" and r.town=:town");
        }
        if (!StringUtils.isBlank(chexingName))
        {
            sql.append(" and c.name like :chexingName");
            countsql.append(" and c.name like:chexingName");
        }

        if (!StringUtils.isBlank(orderBy))
        {
            sql.append(" order by " + orderBy);
            if (!StringUtils.isBlank(orderType))
            {
                sql.append("  " + orderType);
            }
        }
        query = getCurrentSession().createSQLQuery(sql.toString()).addScalar("id", StandardBasicTypes.LONG).addScalar(
                "chexingId", StandardBasicTypes.LONG).addScalar("city", StandardBasicTypes.STRING).addScalar(
                "crateDate", StandardBasicTypes.STRING).addScalar("imageUrl", StandardBasicTypes.STRING).addScalar(
                "name", StandardBasicTypes.STRING).addScalar("province", StandardBasicTypes.STRING).addScalar("sort",
                StandardBasicTypes.INTEGER).addScalar("town", StandardBasicTypes.STRING).addScalar("updateDate",
                StandardBasicTypes.STRING).setResultTransformer(Transformers.aliasToBean(ChenxingCityRelation.class));

        if (!StringUtils.isBlank(province))
        {
            query.setString("province", province);
        }
        if (!StringUtils.isBlank(city))
        {
            query.setString("city", city);
        }
        if (!StringUtils.isBlank(town))
        {
            query.setString("town", town);
        }
        if (!StringUtils.isBlank(chexingName))
        {
            query.setString("chexingName", "%" + chexingName.trim() + "%");
        }
        if (pageSize > 0)
        {
            int from = (currentPage - 1) * pageSize;
            query.setFirstResult(from);
            query.setMaxResults(pageSize);
        }
        List<ChenxingCityRelation> list = query.list();

        query = getCurrentSession().createSQLQuery(countsql.toString())
                .addScalar("counNum", StandardBasicTypes.INTEGER);
        if (!StringUtils.isBlank(province))
        {
            query.setString("province", province);
        }
        if (!StringUtils.isBlank(city))
        {
            query.setString("city", city);
        }
        if (!StringUtils.isBlank(town))
        {
            query.setString("town", town);
        }
        if (!StringUtils.isBlank(chexingName))
        {
            query.setString("chexingName", "%" + chexingName.trim() + "%");
        }
        if (pageSize > 0)
        {
            int totalCount = (Integer) query.uniqueResult();
            int totalPage = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
            json.setTotalSize(totalCount);
            json.setTotalPage(totalPage);
            json.setPageSize(pageSize);
            json.setCurrentPage(currentPage);
        }
        json.setResult(list);
        return json;
    }

    @Override
    public void batchAdd(List<ChenxingCityRelation> list , String province , String city , String town)
    {

        String hql = "delete from  ChenxingCityRelation where province=:province and city=:city";
        if (!StringUtils.isBlank(town))
        {
            hql += " and town=:town ";
        }
        Query query = getCurrentSession().createQuery(hql);
        query.setString("province", province);
        query.setString("city", city);
        if (!StringUtils.isBlank(town))
        {
            query.setString("town", town);
        }
        query.executeUpdate();
        if (null != list && list.size() > 0)
        {
            for (ChenxingCityRelation o : list)
            {
                getCurrentSession().save(o);
            }
        }
    }

    @Override
    public ChenxingCityRelation queryById(Long id)
    {
        StringBuilder sql = new StringBuilder();
        Query query = null;
        sql
                .append("select r.chexingId, r.city,r.crateDate,r.id,c.imageUrl,c.name,r.province,r.sort,r.town,r.updateDate from  t_Chexing_City  r join t_CheXing c on r.chexingId=c.id  where  r.id=:id ");
        query = getCurrentSession().createSQLQuery(sql.toString()).addScalar("id", StandardBasicTypes.LONG).addScalar(
                "chexingId", StandardBasicTypes.LONG).addScalar("city", StandardBasicTypes.STRING).addScalar(
                "crateDate", StandardBasicTypes.STRING).addScalar("imageUrl", StandardBasicTypes.STRING).addScalar(
                "name", StandardBasicTypes.STRING).addScalar("province", StandardBasicTypes.STRING).addScalar("sort",
                StandardBasicTypes.INTEGER).addScalar("town", StandardBasicTypes.STRING).addScalar("updateDate",
                StandardBasicTypes.STRING).setResultTransformer(Transformers.aliasToBean(ChenxingCityRelation.class));
        query.setLong("id", id);
        List<ChenxingCityRelation> list = query.list();
        if (null != list && list.size() > 0)
        {
            return list.get(0);
        }
        return null;
    }

    @Override
    public List<CheXing> queryListByCity(String province , String city , String town)
    {
        Map<Long, Integer> map = null;
        String hql = "from  CheXing order by sort";
        Query query = getCurrentSession().createQuery(hql);
        List<CheXing> list1 = query.list();
        StringBuilder sql = new StringBuilder();
        sql.append("select chexingId from  t_Chexing_City  where  city like :city order by sort ");
        query = getCurrentSession().createSQLQuery(sql.toString()).addScalar("chexingId", StandardBasicTypes.LONG);
        query.setString("city", city + "%");
        List<Long> ids = query.list();
        if (null != ids && ids.size() > 0)
        {
            map = new HashMap<Long, Integer>();
            for (int k = 0; k < ids.size(); k++)
            {
                map.put(ids.get(k), k + 1);
            }
        }
        if (null != list1 && list1.size() > 0)
        {
            for (CheXing o : list1)
            {
                o.setCheck(false);
                if (null != map)
                {
                    if (null != map.get(o.getId()))
                    {
                        o.setSort(map.get(o.getId()));
                        o.setCheck(true);
                    }
                    else
                    {
                        if (null != o.getSort())
                        {
                            o.setSort(o.getSort() + BuzConst.MAXNUM);
                        }
                        else
                        {
                            o.setSort(BuzConst.MAXNUM);
                        }
                    }
                }
                else
                {
                    if (null != o.getSort())
                    {
                        o.setSort(o.getSort() + BuzConst.MAXNUM);
                    }
                    else
                    {
                        o.setSort(BuzConst.MAXNUM);
                    }
                }
            }
        }
        Collections.sort(list1);
        return list1;
    }

    @Override
    public boolean delete(Long[] ids)
    {
        String hql = "delete from  ChenxingCityRelation where id=:id";
        Query query = getCurrentSession().createQuery(hql);
        for (int i = 0; i < ids.length; i++)
        {
            if (null != ids[i])
            {
                query.setLong("id", ids[i]);
                query.executeUpdate();
            }
        }
        return true;
    }

    @Override
    public boolean update(ChenxingCityRelation model)
    {
        if (null != model)
        {
            if (null != model.getId())
            {
                getCurrentSession().update(model);
                return true;
            }
        }
        return false;
    }

    @Override
    public List<CheXing> queryChexByCity(String province , String city , String town)
    {
        Query query = null;
        //先根据城市车型关系查询车型信息
        StringBuilder sql = new StringBuilder();
        sql
                .append("select c.id, c.name,c.imageUrl,c.minImageUrl,c.crateDate,c.updateDate,r.sort,c.detailInfo,c.state from  t_Chexing_City  r join t_CheXing c on r.chexingId=c.id  where  r.city like :city  order by r.sort");
        query = getCurrentSession().createSQLQuery(sql.toString()).addScalar("id", StandardBasicTypes.LONG).addScalar(
                "name", StandardBasicTypes.STRING).addScalar("detailInfo", StandardBasicTypes.STRING).addScalar(
                "imageUrl", StandardBasicTypes.STRING).addScalar("minImageUrl", StandardBasicTypes.STRING).addScalar(
                "crateDate", StandardBasicTypes.STRING).addScalar("updateDate", StandardBasicTypes.STRING).addScalar(
                "sort", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(CheXing.class));
        query.setString("city", city + "%");
        List<CheXing> list = query.list();
        if (null != list && list.size() > 0)
        {
            return list;
        }
        else
        {
            String hql = "from  CheXing where state=:state order by sort";
            query = getCurrentSession().createQuery(hql);
            query.setString("state", BuzConst.CHEXING_STATE_SHOW);//只显示默认展示的车型
            List<CheXing> list1 = query.list();
            return list1;
        }
    }

    @Override
    public List<Long> queryChexingIdsByCity(String city)
    {
        Query query = null;
        //先根据城市车型关系查询车型信息
        String sql = "select chexingId  from  t_Chexing_City where city like :city";
        query = getCurrentSession().createSQLQuery(sql.toString()).addScalar("chexingId", StandardBasicTypes.LONG);
        query.setString("city", city + "%");
        List<Long> list = query.list();
        return list;
    }

}